﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using DAL.Entities;


namespace DAL.Entities_DAL
{
    public class PHIEU_MUON_TRA_DAL:BaseDAL
    {

        

        public bool Delete(string id, string loai)
        {
            string sql = @"set dateformat dmy; DELETE FROM PHIEU_MUON_TRA WHERE MA_PMT = '{0}' AND LOAI_PHIEU = '{1}'";
            sql = string.Format(sql, id, loai);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (PHIEU_MUON_TRA)obj;
            string sql = @"set dateformat dmy; INSERT INTO PHIEU_MUON_TRA(MA_PMT, NGAY_LAP, LOAI_PHIEU, MA_NV, MSSV) VALUES ('{0}', N'{1}', '{2}', '{3}', '{4}')";
            sql = string.Format(sql, o.MA_PMT, o.NGAY_LAP, o.LOAI_PHIEU, o.MA_NV, o.MSSV);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }
        //them sua xoa chi tiet
        public bool Delete_CT(string id, DateTime ngay, string loai, string maphong, string matb)
        {
            string sql = @" set dateformat dmy; DELETE FROM CT_PMT WHERE MA_PMT = '{0}' AND NGAY_LAP = '{1}' AND LOAI_PHIEU = '{2}' AND MA_PHONG = '{3}' AND MA_TB = '{4}' ";
            sql = string.Format(sql, id, ngay, loai, maphong, matb);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert_CT(object obj)
        {
            var o = (CT_PMT)obj;
            string sql = @"set dateformat dmy; INSERT INTO CT_PMT(MA_PMT, NGAY_LAP, LOAI_PHIEU, MA_PHONG, MA_TB, SL_MUON_TRA) VALUES ('{0}', N'{1}', '{2}', '{3}', '{4}', '{5}')";
            sql = string.Format(sql, o.MA_PMT, o.NGAY_LAP, o.LOAI_PHIEU, o.MA_PHONG, o.MA_TB, o.SL_MUON_TRA);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        //cap nhat lai so luong sau khi them vao phieu muon tra
        public bool cap_nhat_CTPMT(int sl, string id, DateTime ngay, string loai, string maphong, string matb)
        {

            var sql = @"set dateformat dmy; UPDATE CT_PMT SET  SL_MUON_TRA= '{0}' WHERE MA_PMT = '{1}' AND NGAY_LAP = '{2}' AND LOAI_PHIEU = '{3}' AND MA_PHONG = '{4}' AND MA_TB = '{5}' ";
            sql = string.Format(sql, sl, id, ngay, loai, maphong, matb);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        //tim ma lon nhat
        public DataTable Select_max()
        {
            var sql = @"SELECT MAX(MA_PMT) as id FROM PHIEU_MUON_TRA";
            return ExecuteQuery(sql);
        }
        //load doi tuong
        public DataTable load_DT()
        {
            string sql = @" 
                            SELECT LOP_HOC AS id ,MSSV as ten FROM DOI_TUONG
                            ";
            return ExecuteQuery(sql);
        }
        //load phong
        public DataTable load_Phong()
        {
            string sql = @" 
                            SELECT distinct a.MA_PHONG AS id ,TEN_PHONG as ten 
                            FROM PHONG a, QUAN_LY_THIET_BI b
                            WHERE a.MA_PHONG = b.MA_PHONG
                            ";
            return ExecuteQuery(sql);
        }

        public DataTable load_qltb(string _phong)
        {
            string sql = @" 
                            SELECT *
                            FROM QUAN_LY_THIET_BI 
                            WHERE MA_PHONG = '{0}'
                            ";
            sql = string.Format(sql, _phong);
            return ExecuteQuery(sql);
        }
        //load phieu muon
        public DataTable load_sttpmt(string maloai)
        {
            string sql = @" 
                            SELECT MA_PMT as id
                            FROM PHIEU_MUON_TRA
                            where LOAI_PHIEU = '{0}'
                            ";
            sql = string.Format(sql, maloai);
            return ExecuteQuery(sql);
        }


        public DataTable load_ctpmt(string id, string loai)
        {
            string sql = @" set dateformat dmy;
                            SELECT *
                            FROM CT_PMT
                            WHERE MA_PMT = '{0}' AND LOAI_PHIEU = '{1}'
                            ";
            sql = string.Format(sql, id, loai);
            return ExecuteQuery(sql);
        }

        public DataTable load_ctpmt1(string ma)
        {
            string sql = @" 
                            SELECT *
                            FROM CT_PMT
                           WHERE MA_PMT = '{0}'
                            ";
            sql = string.Format(sql, ma);
            return ExecuteQuery(sql);
        }
        public DataTable load_pmt(string ma)
        {
            string sql = @" 
                            SELECT *
                            FROM PHIEU_MUON_TRA
                           WHERE MA_PMT = '{0}'
                            ";
            sql = string.Format(sql, ma);
            return ExecuteQuery(sql);
        }

        //in ds phieu muon tra
        public DataTable dsPMT(string mp, DateTime ngay, string loai)
        {
            var sql = @"
                            set dateformat dmy;
                            SELECT b.MA_PMT, b.NGAY_LAP,SL_MUON_TRA, HOTEN_NV, TEN_PHONG, b.LOAI_PHIEU, a.MSSV, LOP_HOC, TEN_TB
                            FROM DOI_TUONG a, PHIEU_MUON_TRA b, CT_PMT c, NHAN_VIEN d, PHONG e, THIET_BI f, QUAN_LY_THIET_BI q
                            WHERE a.mssv = b.mssv and b.MA_PMT = c.MA_PMT and b.ma_nv = d.ma_nv and c.ma_tb = q.ma_tb 
		                            and c.ma_phong = q.ma_phong and e.ma_phong = q.ma_phong and f.ma_tb = q.ma_tb
		                            and b.MA_PMT = '{0}' AND b.NGAY_LAP = '{1}' AND b.LOAI_PHIEU = '{2}'
                            ";
            sql = string.Format(sql, mp, ngay, loai);
            return ExecuteQuery(sql);
        }

        //cap nhat PHIEU TRA
        public bool cap_nhat_pt(string _ma, DateTime ngay)
        {

            var sql = @"set dateformat dmy; UPDATE CT_PMT SET  LOAI_PHIEU= 'PHIEU_TRA' , NGAY_LAP = '{1}' WHERE MA_PMT = '{0}' ";
            sql = string.Format(sql, _ma, ngay);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }
    }
}
